Skip Headers

Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Part Number B14211-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 18 Using Plan Stability

This chapter describes how to use plan stability to preserve performance characteristics. Plan stability also facilitates migration from the rule-based optimizer to the query optimizer when you upgrade to a new Oracle release.

This chapter contains the following topics:

- Using Plan Stability to Preserve Execution Plans

- Using Plan Stability with Query Optimizer Upgrades

## 18.1 Using Plan Stability to Preserve Execution Plans

Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes in optimizer statistics, changes to the optimizer mode settings, and changes to parameters affecting the sizes of memory structures, such as `SORT_AREA_SIZE` and `BITMAP_MERGE_AREA_SIZE`. Plan stability is most useful when you cannot risk any performance changes in an application.

Plan stability preserves execution plans in stored outlines. An outline is implemented as a set of optimizer hints that are associated with the SQL statement. If the use of the outline is enabled for the statement, Oracle automatically considers the stored hints and tries to generate an execution plan in accordance with those hints.

Oracle can create a public or private stored outline for one or all SQL statements. The optimizer then generates equivalent execution plans from the outlines when you enable the use of stored outlines. You can group outlines into categories and control which category of outlines Oracle uses to simplify outline administration and deployment.

The plans Oracle maintains in stored outlines remain consistent despite changes to a system's configuration or statistics. Using stored outlines also stabilizes the generated execution plan if the optimizer changes in subsequent Oracle releases.

Note: If you develop applications for mass distribution, then you can use stored outlines to ensure that all customers access the same execution plans.

### 18.1.1 Using Hints with Plan Stability

The degree to which plan stability controls execution plans is dictated by how much the Oracle hint mechanism controls execution plans, because Oracle uses hints to record stored plans.

There is a one-to-one correspondence between SQL text and its stored outline. If you specify a different literal in a predicate, then a different outline applies. To avoid this, replace literals in applications with bind variables.

See Also: Oracle can allow similar statements to share SQL by replacing literals with system-generated bind variables. This works with plan stability if the outline was generated using the `CREATE_STORED_OUTLINES` parameter, not the `CREATE` `OUTLINE` statement. Also, the outline must have been created with the `CURSOR_SHARING` parameter set to `SIMILAR`, and the parameter must also set to `SIMILAR` when attempting to use the outline. See Chapter 7, "Memory Configuration and Use" for more information.

Plan stability relies on preserving execution plans at a point in time when performance is satisfactory. In many environments, however, attributes for datatypes such as `dates` or `order numbers` can change rapidly. In these cases, permanent use of an execution plan can result in performance degradation over time as the data characteristics change.

This implies that techniques that rely on preserving plans in dynamic environments are somewhat contrary to the purpose of using query optimization. Query optimization attempts to produce execution plans based on statistics that accurately reflect the state of the data. Thus, you must balance the need to control plan stability with the benefit obtained from the optimizer's ability to adjust to changes in data characteristics.

#### 18.1.1.1 How Outlines Use Hints

An outline consists primarily of a set of hints that is equivalent to the optimizer's results for the execution plan generation of a particular SQL statement. When Oracle creates an outline, plan stability examines the optimization results using the same data used to generate the execution plan. That is, Oracle uses the input to the execution plan to generate an outline, and not the execution plan itself.

Note: Oracle creates the `USER_OUTLINES` and `USER_OUTLINE_HINTS` views in the `SYS` tablespace based on data in the `OL$` and `OL$HINTS` tables, respectively. Direct manipulation of the `OL$`, `OL$HINTS`, and `OL$NODES` tables is prohibited. You can embed hints in SQL statements, but this has no effect on how Oracle uses outlines. Oracle considers a SQL statement that you revised with hints to be different from the original SQL statement stored in the outline.

### 18.1.2 Storing Outlines

Oracle stores outline data in the `OL$`, `OL$HINTS`, and `OL$NODES` tables. Unless you remove them, Oracle retains outlines indefinitely.

The only effect outlines have on caching execution plans is that the outline's category name is used in addition to the SQL text to identify whether the plan is in cache. This ensures that Oracle does not use an execution plan compiled under one category to execute a SQL statement that Oracle should compile under a different category.

### 18.1.3 Enabling Plan Stability

Settings for several parameters, especially those ending with the suffix `_ENABLED`, must be consistent across execution environments for outlines to function properly. These parameters are:

- `QUERY_REWRITE_ENABLED`

- `STAR_TRANSFORMATION_ENABLED`

- `OPTIMIZER_FEATURES_ENABLE`

### 18.1.4 Using Supplied Packages to Manage Stored Outlines

The `DBMS_OUTLN` and `DBMS_OUTLN_EDIT` package provides procedures used for managing stored outlines and their outline categories.

Users need the `EXECUTE_CATALOG_ROLE` role to execute `DBMS_OUTLN`, but public has execute privileges on `DBMS_OUTLN_EDIT`. The `DBMS_OUTLN_EDIT` package is an invoker's rights package.

Some of the useful `DBMS_OUTLN` and `DBMS_OUTLN_EDIT` procedures are:

- `CLEAR_USED` - Clears specified outline

- `DROP_BY_CAT` - Drops outlines that belong to a specified category

- `UPDATE_BY_CAT` - Changes the category of outlines in one specified category to a new specified category

- `EXACT_TEXT_SIGNATURES` - Computes an outline signature according to an exact text matching scheme

- `GENERATE_SIGNATURE` - Generates a signature for the specified SQL text

  See Also: Oracle Database PL/SQL Packages and Types Reference for detailed information on using `DBMS_OUTLN` package procedures Oracle Database PL/SQL Packages and Types Reference for detailed information on using `DBMS_OUTLN_EDIT` package procedures

### 18.1.5 Creating Outlines

Oracle can automatically create outlines for all SQL statements, or you can create them for specific SQL statements. In either case, the outlines derive their input from the optimizer.

Oracle creates stored outlines automatically when you set the initialization parameter `CREATE_STORED_OUTLINES` to `true`. When activated, Oracle creates outlines for all compiled SQL statements. You can create stored outlines for specific statements using the `CREATE` `OUTLINE` statement.

When creating or editing a private outline, the outline data is written to global temporary tables in the `SYSTEM` schema. These tables are accessible with the `OL$`, `OL$HINTS`, and `OL$NODES` synonyms.

Note: You must ensure that schemas in which outlines are to be created have the `CREATE` `ANY` `OUTLINE` privilege. Otherwise, despite having turned on the `CREATE_STORED_OUTLINE` initialization parameter, you will not find outlines in the database after you run the application. Also, the default system tablespace can become exhausted if the `CREATE_STORED_OUTLINES` initialization parameter is enabled and the running application has an abundance of literal SQL statements. If this happens, use the `DBMS_OUTLN`.`DROP_UNUSED` procedure to remove those literal SQL outlines.

See Also: Oracle Database SQL Reference for more information on the `CREATE` `OUTLINE` statement Oracle Database PL/SQL Packages and Types Reference for more information on the `DBMS_OUTLN` and `DBMS_OUTLN_EDIT` packages "Moving from RBO to the Query Optimizer" for information on moving from the rule-based optimizer to the query optimizer Oracle Enterprise Manager Concepts for information on the Outline Management and Outline Editor tools, which let you create, edit, delete, and manage stored outlines with an easy-to-use graphical interface

#### 18.1.5.1 Using Category Names for Stored Outlines

Outlines can be categorized to simplify the management task. The `CREATE` `OUTLINE` statement allows for specification of a category. The `DEFAULT` category is chosen if unspecified. Likewise, the `CREATE_STORED_OUTLINES` initialization parameter lets you specify a category name, where specifying `true` produces outlines in the `DEFAULT` category.

If you specify a category name using the `CREATE_STORED_OUTLINES` initialization parameter, then Oracle assigns all subsequently created outlines to that category until you reset the category name. Set the parameter to `false` to suspend outline generation.

If you set `CREATE_STORED_OUTLINES` to `true`, or if you use the `CREATE` `OUTLINE` statement without a category name, then Oracle assigns outlines to the category name of `DEFAULT`.

### 18.1.6 Using and Editing Stored Outlines

When you activate the use of stored outlines, Oracle always uses the query optimizer. This is because outlines rely on hints, and to be effective, most hints require the query optimizer.

To use stored outlines when Oracle compiles a SQL statement, set the system parameter `USE_STORED_OUTLINES` to `true` or to a category name. If you set `USE_STORED_OUTLINES` to `true`, then Oracle uses outlines in the `default` category. If you specify a category with the `USE_STORED_OUTLINES` parameter, then Oracle uses outlines in that category until you reset the parameter to another category name or until you suspend outline use by setting `USE_STORED_OUTLINES` to `false`. If you specify a category name and Oracle does not find an outline in that category that matches the SQL statement, then Oracle searches for an outline in the `default` category.

If you want to use a specific outline rather than all the outlines in a category, use the `ALTER` `OUTLINE` statement to enable the specific outline. If you want to use the outlines in a category except for a specific outline, use the `ALTER` `OUTLINE` statement to disable the specific outline in the category that is being used. The `ALTER` `OUTLINE` statement can also rename a stored outline, reassign it to a different category, or regenerate it.

See Also: Oracle Database SQL Reference for information on the `ALTER` `OUTLINE` statement

The designated outlines only control the compilation of SQL statements that have outlines. If you set `USE_STORED_OUTLINES` to `false`, then Oracle does not use outlines. When you set `USE_STORED_OUTLINES` to `false` and you set `CREATE_STORED_OUTLINES` to `true`, Oracle creates outlines but does not use them.

The `USE_PRIVATE_OUTLINES` parameter lets you control the use of private outlines. A private outline is an outline seen only in the current session and whose data resides in the current parsing schema. Any changes made to such an outline are not seen by any other session on the system, and applying a private outline to the compilation of a statement can only be done in the current session with the `USE_PRIVATE_OUTLINES` parameter. Only when you explicitly choose to save your edits back to the public area are they seen by the rest of the users.

While the optimizer usually chooses optimal plans for queries, there are times when users know things about the execution environment that are inconsistent with the heuristics that the optimizer follows. By editing outlines directly, you can tune the SQL query without having to alter the application.

When the `USE_PRIVATE_OUTLINES` parameter is enabled and an outlined SQL statement is issued, the optimizer retrieves the outline from the session private area rather than the public area used when `USE_STORED_OUTLINES` is enabled. If no outline exists in the session private area, then the optimizer will not use an outline to compile the statement.

Any `CREATE` `OUTLINE` statement requires the `CREATE` `ANY` `OUTLINE` privilege. Specification of the `FROM` clause also requires the `SELECT` privilege. This privilege should be granted only to those users who would have the authority to view SQL text and hint text associated with the outlined statements. This role is required for the `CREATE` `OUTLINE` `FROM` command unless the issuer of the command is also the owner of the outline.

When you begin an editing session, `USE_PRIVATE_OUTLINES` should be set to the category to which the outline being edited belongs. When you are finished editing, this parameter should be set to `false` to restore the session to normal outline lookup according to the `USE_STORED_OUTLINES` parameter.

Note: The `USE_STORED_OUTLINES` and `USE_PRIVATE_OUTLINES` parameters are system or session specific. They are not initialization parameters. For more information on these parameters, see the Oracle Database SQL Reference.

You also can use the Oracle Enterprise Manager Outline Editor to update outlines.

See Also: Oracle Enterprise Manager Concepts for information on Oracle Enterprise Manager GUI tools

#### 18.1.6.1 Example of Editing Outlines

Assume that you want to edit the outline `ol1`. The steps are as follows:

1. Connect to a schema from which the outlined statement can be executed, and ensure that the `CREATE` `ANY` `OUTLINE` and `SELECT` privileges have been granted.

2. Clone the outline being edited to the private area using the following:

       CREATE PRIVATE OUTLINE p_ol1 FROM ol1;

3. Edit the outline, either with the Outline Editor in Enterprise Manager or manually using DBMS_OUTLN_EDIT. If you want to change join order, modify the appropriate `LEADING` hint. See "Hints for Join Orders".

4. If manually editing the outline, use DBMS_OUTLN_EDIT.CHANGE_JOIN_POS to change the position; then resynchronize the stored outline definition using either of the following so-called identity statements:

       exec DBMS_OUTLN_EDIT.REFRESH_PRIVATE_OUTLINE ('S-I1');

       CREATE PRIVATE OUTLINE p_ol1 FROM PRIVATE p_ol1;

   You can also use `DBMS_OUTLN_EDIT`.`REFRESH_PRIVATE_OUTLINE` or `ALTER` `SYSTEM` `FLUSH` `SHARED_POOL` to accomplish this.

5. Test the edits. Set `USE_PRIVATE_OUTLINES=TRUE`, and issue the outline statement or run `EXPLAIN` `PLAN` on the statement.

6. If you want to preserve these edits for public use, then publicize the edits with the following statement.

       CREATE OR REPLACE OUTLINE ol1 FROM PRIVATE p_ol1;

7. Disable private outline usage by setting the following:

       USE_PRIVATE_OUTLINES=FALSE

   See Also: Oracle Database Reference for syntax when using the `CREATE_STORED_OUTLINES` initialization parameter Oracle Database SQL Reference for SQL syntax when using the `USE_STORED_OUTLINES` and `USE_PRIVATE_OUTLINES` parameters Oracle Database PL/SQL Packages and Types Reference for more information on the `DBMS_OUTLN` and `DBMS_OUTLN_EDIT` packages

#### 18.1.6.2 How to Tell If an Outline Is Being Used

You can test if an outline is being used with the `V$SQL` view. Query the `OUTLINE_CATEGORY` column in conjunction with the SQL statement. If an outline was applied, then this column contains the category to which the outline belongs. Otherwise, it is `NULL`. The `OUTLINE_SID` column tells you if this particular cursor is using a public outline (value is 0) or a private outline (session's SID of the corresponding session using it).

For example:

    SELECT OUTLINE_CATEGORY, OUTLINE_SID
      FROM V$SQL
      WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM emp%';

### 18.1.7 Viewing Outline Data

You can access information about outlines and related hint data that Oracle stores in the data dictionary from the following views``:

- `USER_OUTLINES`

- `USER_OUTLINE_HINTS`

- `ALL_OUTLINES`

- `ALL_OUTLINE_HINTS`

- `DBA_OUTLINES`

- `DBA_OUTLINE_HINTS`

Use the following syntax to obtain outline information from the `USER_OUTLINES` view, where the outline category is `mycat`:

    SELECT NAME, SQL_TEXT
      FROM USER_OUTLINES
      WHERE CATEGORY='mycat';

Oracle responds by displaying the names and text of all outlines in category `mycat`.

To see all generated hints for the outline `name1`, use the following syntax:

    SELECT HINT
      FROM USER_OUTLINE_HINTS
      WHERE NAME='name1';

You can check the flags in `_OUTLINES` views for information on compatibility, format, and whether an outline is enabled. For example, check the `ENABLED` field in the `USER_OUTLINES` view to determine whether an outline is enabled or not.

    SELECT NAME, CATEGORY, ENABLED FROM USER_OUTLINES;

See Also: Oracle Database Reference for information on views related to outlines

### 18.1.8 Moving Outline Tables

Oracle creates the `USER_OUTLINES` and `USER_OUTLINE_HINTS` views based on data in the `OL$` and `OL$HINTS` tables, respectively. Oracle creates these tables, and also the `OL$NODES` table, in the `SYSTEM` tablespace using a schema called `OUTLN`. If outlines use too much space in the `SYSTEM` tablespace, then you can move them. To do this, create a separate tablespace and move the outline tables into it using the following process.

1. The default system tablespace could become exhausted if the `CREATE_STORED_OUTLINES` parameter is on and if the running application has many literal SQL statements. If this happens, then use the `DBMS_OUTLN`.`DROP_UNUSED` procedure to remove those literal SQL outlines.

1. Use the Oracle Export utility to export the `OL$`, `OL$HINTS`, and `OL$NODES` tables:

       EXP OUTLN/outln_password
           FILE = exp_file TABLES = 'OL$' 'OL$HINTS' 'OL$NODES'

2. Start SQL*Plus and connect to the database.

       CONNECT OUTLN/outln_password;

3. Remove the previous `OL$`, `OL$HINTS`, and `OL$NODES` tables:

       DROP TABLE OL$;
       DROP TABLE OL$HINTS;
       DROP TABLE OL$NODES;

1. Create a new tablespace for the tables:

       CONNECT SYSTEM/system_password;
       CREATE TABLESPACE outln_ts
         DATAFILE 'tspace.dat' SIZE 2M
         DEFAULT STORAGE (INITIAL 10K NEXT 20K MINEXTENTS 1 MAXEXTENTS 999
                          PCTINCREASE 10)
         ONLINE;

2. Enter the following statement to change the default tablespace:

       ALTER USER OUTLN DEFAULT TABLESPACE outln_ts;

3. To force the import into the `OUTLN_TS` tablespace, set quota for the SYSTEM tablespace to `0K` for the `OUTLN` user. You will also need to revoke the UNLIMITED TABLESPACE privilege and all roles, such as the RESOURCE role, that have unlimited tablespace privileges or quotas. Set a quota for the `OUTLN` tablespace.

4. Import the `OL$`, `OL$HINTS`, and `OL$NODES` tables:

       IMP OUTLN/outln_password
           FILE = exp_file TABLES = (OL$, OL$HINTS, OL$NODES)

When the import process has finished, the `OL$`, `OL$HINTS`, and `OL$NODES` tables are re-created in the schema named `OUTLN` and now reside in a new tablespace called `OUTLN_TS`.

At the completion of the process, you may want to adjust the tablespace quotas for the `OUTLN` user appropriately by adding any privileges and roles that were removed in a previous step.

See Also: Oracle Database Utilities for detailed information on using the `EXPORT` and `IMPORT` utilities, note the section on reorganizing tablespaces under the discussion of the `IMPORT` utility Oracle Database PL/SQL Packages and Types Reference for detailed information on using the `DBMS_OUTLN` package

## 18.2 Using Plan Stability with Query Optimizer Upgrades

This section describes procedures you can use to significantly improve performance by taking advantage of query optimizer functionality. Plan stability provides a way to preserve a system's targeted execution plans with satisfactory performance while also taking advantage of new query optimizer features for the rest of the SQL statements.

While there are classes of SQL statements and features where an exact reproduction of the original execution plan is not guaranteed, plan stability can still be a highly useful part of the migration process. Before the migration, outline capturing of execution plan should be turned on until all or most of the applications SQL-statement have been covered. If, after the migration, there are performance problems for some specific SQL-statement, the use of the stored outline for that statement can be turned on as a way of restoring the old behavior. The use of stored outlines is not always the best way of resolving a migration related performance problem because it prevents plans from adapting to changing data properties, but it adds to the arsenal of techniques that can be used to address such problems.

Topics covered in this section are:

- Moving from RBO to the Query Optimizer

- Moving to a New Oracle Release under the Query Optimizer

### 18.2.1 Moving from RBO to the Query Optimizer

If an application was developed using the rule-based optimizer, then a considerable amount of effort might have gone into manually tuning the SQL statements to optimize performance. You can use plan stability to leverage the effort that has already gone into performance tuning by preserving the behavior of the application when upgrading from rule-based to query optimization.

By creating outlines for an application before switching to query optimization, the plans generated by the rule-based optimizer can be used, while statements generated by newly written applications developed after the switch use query plans. To create and use outlines for an application, use the following process.

Note: Carefully read this procedure and consider its implications before executing it!

1. Ensure that schemas in which outlines are to be created have the `CREATE` `ANY` `OUTLINE` privilege. For example, from `SYS`:

       GRANT CREATE ANY OUTLINE TO user-name

1. Execute syntax similar to the following to designate; for example, the `RBOCAT` outline category.

       ALTER SESSION SET CREATE_STORED_OUTLINES = rbocat;

2. Run the application long enough to capture stored outlines for all important SQL statements.

3. Suspend outline generation:

       ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

4. Gather statistics with the `DBMS_STATS` package.

5. Alter the parameter `OPTIMIZER_MODE` to `CHOOSE`.

6. Enter the following syntax to make Oracle use the outlines in category `RBOCAT`:

       ALTER SESSION SET USE_STORED_OUTLINES = rbocat;

7. Run the application.

   Subject to the limitations of plan stability, access paths for this application's SQL statements should be unchanged.

   Note: If a query was not executed in step 2, then you can capture the old behavior of the query even after switching to query optimization. To do this, change the optimizer mode to `RULE`, create an outline for the query, and then change the optimizer mode back to `CHOOSE`.

### 18.2.2 Moving to a New Oracle Release under the Query Optimizer

When upgrading to a new Oracle release under query optimization, there is always a possibility that some SQL statements will have their execution plans changed due to changes in the optimizer. While such changes benefit performance, you might have applications that perform so well that you would consider any changes in their behavior to be an unnecessary risk. For such applications, you can create outlines before the upgrade using the following procedure.

Note: Carefully read this procedure and consider its implications before running it!

1. Enter the following syntax to enable outline creation:

       ALTER SESSION SET CREATE_STORED_OUTLINES = ALL_QUERIES;

2. Run the application long enough to capture stored outlines for all critical SQL statements.

3. Enter this syntax to suspend outline generation:

       ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

4. Upgrade the production system to the new version of the RDBMS.

5. Run the application.

After the upgrade, you can enable the use of stored outlines, or alternatively, you can use the outlines that were stored as a backup if you find that some statements exhibit performance degradation after the upgrade.

With the latter approach, you can selectively use the stored outlines for such problematic statements as follows:

1. For each problematic SQL statement, change the `CATEGORY` of the associated stored outline to a category name similar to this:

       ALTER OUTLINE outline_name CHANGE CATEGORY TO problemcat;

2. Enter this syntax to make Oracle use outlines from the category `problemcat`.

       ALTER SESSION SET USE_STORED_OUTLINES = problemcat;

#### 18.2.2.1 Upgrading with a Test System

A test system, separate from the production system, can be useful for conducting experiments with optimizer behavior in conjunction with an upgrade. You can migrate statistics from the production system to the test system using import/export. This can alleviate the need to fill the tables in the test system with data.

You can move outlines between the systems by category. For example, after you create outlines in the `problemcat` category, export them by category using the query-based export option. This is a convenient and efficient way to export only selected outlines from one database to another without exporting all outlines in the source database. To do this, issue these statements:

    EXP OUTLN/outln_password FILE=exp-file TABLES= 'OL$' 'OL$HINTS' 'OL$NODES'
    QUERY='WHERE CATEGORY="problemcat"'